package jude.java.money.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPatternFormatting;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jxcell.CellException;
import com.jxcell.View;
import com.sun.org.apache.xerces.internal.impl.dv.util.Base64;

public class ExcelExporter {

	private static final String NUMBER_FORMAT = "#,##0.00";		// 定制浮点数格式
	private static final String KEY = "Z2V0UGFzcyghM3F2emxkZ2xt";

	private String xlsFileName;
	private HSSFWorkbook workbook;
	private HSSFSheet sheet;
	private HSSFRow row;
	private CellStyle defaultStyle;
	private List<int[]> dateCells;
	private List<int[]> date2Cells;

	/**
	 * 初始化Excel
	 * @param fileName 导出文件名
	 * @param sheetName 
	 */
	public ExcelExporter(String fileName, String sheetName) {
		this(null, fileName, sheetName);
	}
	/**
	 * 初始化Excel
	 * @param fileName 导出文件名
	 * @param sheetName 
	 */
	public ExcelExporter(String tplFile, String fileName, String sheetName) {
//		copyTpl(tplFile, fileName);
		InputStream in = null;
		if (tplFile != null && new File(tplFile).exists()) {
			try {
				in = new FileInputStream(tplFile);
				this.workbook = new HSSFWorkbook(in);
				this.sheet = workbook.getSheetAt(0);
			} catch (IOException e) {
			}
		}
		this.xlsFileName = fileName;
		if (this.workbook == null) {
			this.workbook = new HSSFWorkbook();
			this.sheet = workbook.createSheet(sheetName);
		}
	}
	
	public ExcelExporter(String fileName) {
		this(fileName, null);
	}
	
	public HSSFWorkbook getWorkbook() {
		return this.workbook;
	}
	
	public void setDefaultStyle(CellStyle defaultStyle) {
		this.defaultStyle = defaultStyle;
	}
	
	public void createSheet(String name) {
		this.sheet = workbook.createSheet(name);
		this.row = null;
		this.dateCells = null;
		this.date2Cells = null;
	}
	
	public void setColumnWidth(int... widths) {
		for (int i = 0; i < widths.length; i++) {
			int width = widths[i];
			if (width > 0) {
				sheet.setColumnWidth(i, Math.min(width*40, 30000));		//max 65280
			} else if (width == 0) {
				sheet.autoSizeColumn(i);
			}
		}
	}
	
	public void freeze(int row, int col) {
		sheet.createFreezePane(col, row);
	}
	
	public void merge(int firstCol, int lastCol) {
		merge(-1, -1, firstCol, lastCol);
	}
	
	public void merge(int rows, int firstCol, int lastCol) {
//		sheet.addMergedRegion(new Region(0,   (short)0,   0,   (short)2));
		int firstRow = this.row.getRowNum();
		int lastRow = firstRow + rows - 1;
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}
	
	public void merge(int firstRow, int lastRow, int firstCol, int lastCol) {
//		sheet.addMergedRegion(new Region(0,   (short)0,   0,   (short)2));
		if (firstRow <= -1)
			firstRow = this.row.getRowNum();
		if (lastRow <= -1)
			lastRow = this.row.getRowNum();
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	/**
	 * 导出Excel文件
	 */
	public void exportXLS() throws XLSException {
		try {
//			workbook.writeProtectWorkbook(KEY.replace("{11}", "zl").replace("{22}", "dglm"), "Jude");
//			sheet.setActive(true);
			File tmp = new File(xlsFileName);
			FileOutputStream fOut = new FileOutputStream(tmp);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
			
//			setReadonly(KEY);
			setProtect(getPass());
		} catch (FileNotFoundException e) {
			throw new XLSException(" 生成导出Excel文件出错! ", e);
		} catch (IOException e) {
			throw new XLSException(" 写入Excel文件出错! ", e);
		} catch (CellException e) {
			throw new XLSException(" 加密出错! ", e);
		} catch (Exception e) {
			throw new XLSException(" 其它错! ", e);
		}
	}
	
	private String getPass() {
		return new String(Base64.decode(KEY)).substring(8);
	}
	
	/** 只读保护 */
	public void setReadonly(String pass) throws IOException {
		if (StringUtils.isEmpty(pass))
			return;
		File f = new File(xlsFileName);
		File tmp = new File(xlsFileName + ".tmp");
		f.renameTo(tmp);
		FileInputStream in = new FileInputStream(tmp);
		HSSFWorkbook wb = new HSSFWorkbook(in);
		wb.writeProtectWorkbook(pass, "Jude");
		FileOutputStream fOut = new FileOutputStream(xlsFileName);
		wb.write(fOut);
		fOut.flush();
		fOut.close();
		in.close();
		tmp.delete();
	}
	
	/** 密码保护 */
	public void setProtect(String pass) throws IOException, CellException {
		if (StringUtils.isEmpty(pass))
			return;
		File f = new File(xlsFileName);
		File tmp = new File(xlsFileName + ".tmp");
		f.renameTo(tmp);
		View view = new View();
		view.read(tmp.getPath());
		view.editCopyRight();
		view.write(xlsFileName, pass);
		tmp.delete();
	}
	
	public void setStyle(int row, int col, CellStyle style) {
		HSSFRow r = this.row;
		setRow(row);
		HSSFCell cell = getCell(col);
		cell.setCellStyle(style);
		this.row = r;
	}
	public void setStyle(int col, CellStyle style) {
		HSSFCell cell = getCell(col);
		cell.setCellStyle(style);
	}

	/**
	 * 增加一行
	 * @param index 行号
	 */
	public void setRow(int index) {
		int last = this.sheet.getLastRowNum();
		if (last > index) {
			this.row = this.sheet.getRow(index);
		} else {
			this.row = this.sheet.createRow(index);
		}
	}
	
	public HSSFCell getCell(int index) {
		if (this.row == null)
			createRow(-1);
		HSSFCell cell = this.row.getCell(index);
		if (cell == null) {
			cell = this.row.createCell(index);
			cell.setCellStyle(defaultStyle);
		}
		return cell;
	}
	
	public void createRow() {
		createRow(-1);
	}
	public void createRow(int row) {
		if (row < 0)
			row = this.sheet.getLastRowNum() + 1;
		this.row = this.sheet.createRow(row);
		this.row.setHeight((short)(16*20));
	}

	/**
	 * 设置单元格
	 * @param index 列号
	 * @param value 单元格填充值
	 */
	public void setCell(int index, String value) {
		setCell(index, value, null, -1);
	}
	
	public void setCell(int index, String value, CellStyle style) {
		setCell(index, value, style, -1);
	}
	
	public void setCell(int index, String value, CellStyle style, int width) {
		HSSFCell cell = getCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		if (style != null) {
			cell.setCellStyle(style);
//			if (width <= 0)
//				sheet.autoSizeColumn(index);
		}
		if (width > 0) {
			sheet.setColumnWidth(index, Math.min(width*40, 30000));		//max 65280
		} else if (width == 0) {
			sheet.autoSizeColumn(index);
		}
	}

	/**
	 * 设置单元格
	 * @param index 列号
	 * @param date 单元格填充值
	 */
	public void setCell(int index, Date date) {
		setCell(index, date, null);
	}
	public void setCell(int index, Date date, CellStyle style) {
		HSSFCell cell = getCell(index);
		if (date != null) {
			cell.setCellValue(date);
			if (dateCells == null)
				dateCells = new ArrayList<int[]>();
			dateCells.add(new int[] {cell.getRowIndex(), cell.getColumnIndex()});
		}
		cell.setCellStyle(getDateStyle(style));
	}
	
	public CellStyle getDateStyle(CellStyle style) {
		CellStyle s = workbook.createCellStyle();
		if (style != null)
			s.cloneStyleFrom(style);
		HSSFDataFormat format= workbook.createDataFormat();
		s.setDataFormat(format.getFormat("yyyy-MM-dd"));
		s.setAlignment(CellStyle.ALIGN_CENTER);
		return s;
	}

	/**
	 * 设置单元格
	 * @param index 列号
	 * @param value 单元格填充值
	 */
	public void setCell(int index, int value) {
		HSSFCell cell = getCell(index);
//		cell.setHyperlink(new LinkedDataFormulaField());
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * @param index 列号
	 * @param value 单元格填充值
	 */
	public void setCell(int index, double value) {
		HSSFCell cell = getCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		HSSFCellStyle cellStyle = workbook.createCellStyle();		// 建立新的cell样式
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));	// 设置cell样式为定制的浮点数格式
		cell.setCellStyle(cellStyle);								// 设置该cell浮点数的显示格式
	}
	
	public void setFormula(int index, String formula) {
		setFormula(index, formula, null);
	}
	public void setFormula(int index, String formula, CellStyle style) {
		setFormula(index, formula, style, false);
	}
	public void setFormula(int index, String formula, CellStyle style, boolean date) {
		HSSFCell cell = getCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
		cell.setCellFormula(formula);
		if (style != null) {
			cell.setCellStyle(style);
		}
		if (date) {
			if (date2Cells == null)
				date2Cells = new ArrayList<int[]>();
			date2Cells.add(new int[] {cell.getRowIndex(), cell.getColumnIndex()});
		}
	}
	public void setHyperlink(int index, String formula, CellStyle style) {
		HSSFCell cell = getCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
		HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT);
		link.setAddress(formula);
		cell.setHyperlink(link);
		cell.setCellFormula(formula);
		if (style != null) {
			cell.setCellStyle(style);
		}
	}
	
	public void setComment(int index, String comment) {
		if (StringUtils.isNotEmpty(comment)) {
			HSSFPatriarch patr = sheet.createDrawingPatriarch();
			HSSFComment c = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short)6, 5));
			c.setString(new HSSFRichTextString(comment));
			HSSFCell cell = getCell(index);
			cell.setCellComment(c);
		}
	}
	
	public void protectSheet(String pass) {
		if (sheet != null) {
			sheet.protectSheet(pass == null ? "" : pass);
		}
	}
	
	public void applyDateConditionFormatting() {
		if (dateCells != null && !dateCells.isEmpty()) {
			CellRangeAddress[] address = new CellRangeAddress[dateCells.size()];
			int i = 0;
			for (int[] cell : dateCells) {
				address[i++] = new CellRangeAddress(cell[0], cell[0], cell[1], cell[1]);
			}
			HSSFSheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
			HSSFConditionalFormattingRule rule = cf.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "TODAY()-10", "TODAY()+10");
			HSSFPatternFormatting formatting = rule.createPatternFormatting();
			formatting.setFillBackgroundColor((short)10);
			cf.addConditionalFormatting(address, rule);
		}
		if (date2Cells != null && !date2Cells.isEmpty()) {
			CellRangeAddress[] address = new CellRangeAddress[date2Cells.size()];
			int i = 0;
			for (int[] cell : date2Cells) {
				address[i++] = new CellRangeAddress(cell[0], cell[0], cell[1], cell[1]);
//				address[i++] = new CellRangeAddress(cell[0], cell[0], 1, cell[1] + 1);
			}
			HSSFSheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
			HSSFConditionalFormattingRule rule = cf.createConditionalFormattingRule(ComparisonOperator.LE, "TODAY()", null);
			HSSFPatternFormatting formatting = rule.createPatternFormatting();
//			formatting.setFillForegroundColor((short)22);
//			formatting.setFillPattern((short)0);
			formatting.setFillBackgroundColor((short)22);
			cf.addConditionalFormatting(address, rule);
		}
	}
	
/*	private static void copyTpl(String tplFile, String fileName) {
		if (StringUtils.isEmpty(tplFile))
			return;
		File src = new File(tplFile);
		if (!src.exists())
			return;
		File dest = new File(fileName);
		try {
			FileUtils.copyFile(src, dest, true);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
*/
	public static class XLSException extends RuntimeException {
		private static final long serialVersionUID = 1L;

		public XLSException() {
			super();
		}

		public XLSException(String message, Throwable cause) {
			super(message, cause);
		}

		public XLSException(String message) {
			super(message);
		}

		public XLSException(Throwable cause) {
			super(cause);
		}

	}
}